const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');

const app = express();

// 使用CORS中间件处理跨域请求
app.use(cors());

// 使用JSON中间件解析请求体中的JSON数据
app.use(express.json());


const connection = mysql.createConnection({
  host: 'mysql5.sqlpub.com',
  port: 3310,
  user: 'yifang',
  password: 'TKqlA0BIF4LHO3VO',
  database: 'wuyifang' 
});



// 连接数据库，并在连接成功时输出日志
connection.connect(err => {
  if (err) throw err;
  console.log('数据库连接成功！！');
});

app.use((req, res, next) => {
  res.header("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Headers", "Content-Type");
  next();
});


// 登录接口
app.post('/api/login', async (req, res) => {
  const { username, password } = req.body;
  console.log('收到登录请求1:', req.body); // 添加日志
 try {
    const [rows] = await connection.promise().query(  // 修改这里
      'SELECT * FROM users WHERE username = ? AND password = ?',
      [username, password]
    );
    
    if (rows.length === 0) {
      return res.json({ success: false, message: '用户名或密码错误' });
    }
    
    const user = rows[0];
    // 生成简单token(实际项目中应该用更安全的方式)
    const token = Buffer.from(`${username}:${Date.now()}`).toString('base64');
    
    res.json({
      success: true,
      token,
      isSuperAdmin: user.is_super_admin,
      username: user.username
    });
    
  } catch (error) {
    console.error('登录错误:', error);
    res.status(500).json({ success: false, message: '服务器错误' });
  }
});

// 查询书籍
app.post('/api/books', (req, res) => {
  console.log('[调试] POST请求到达 /api/books');
  const sql = 'SELECT id, book_name as bookName, author, synopsis, read_count as bookNumber FROM books';
  
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('数据库错误:', err.message);
      return res.status(500).json({ 
        success: false, 
        message: '数据库查询失败',
        error: err.message
      });
    }
    console.log('[调试] 查询结果:', results);
    res.json({ success: true, data: results });
  });
});

// 添加新书接口
app.post('/api/books/add', (req, res) => {
  console.log('[添加书籍] 请求数据:', req.body);
  const { bookName, author, synopsis, bookNumber } = req.body;
  
  const sql = 'INSERT INTO books (book_name, author, synopsis, read_count) VALUES (?, ?, ?, ?)';
  connection.query(sql, [bookName, author, synopsis, bookNumber || 0], (err, result) => {
    if (err) {
      console.error('添加书籍失败:', err);
      return res.status(500).json({ success: false, message: '添加书籍失败' });
    }
    res.json({ success: true, message: '添加书籍成功', data: { id: result.insertId } });
  });
});

// 查询管理员接口
app.post('/api/admins', (req, res) => {
  const sql = 'SELECT username, password, is_super_admin as role, created_at as created, updated_at as updated FROM users';
  
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('数据库错误:', err.message);
      return res.status(500).json({ 
        success: false, 
        message: '数据库查询失败',
        error: err.message
      });
    }

    res.json({ success: true, data: results });
  });
});


// 添加管理员接口
app.post('/api/admins/add', (req, res) => {
  const { username, password, role } = req.body;

  // 检查参数是否完整
  if (!username || !password) {
    return res.status(400).json({ success: false, message: '账号和密码不能为空' });
  }

  const sql = 'INSERT INTO users (username, password, is_super_admin, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW())';
  const values = [username, password, role || 0]; // 默认普通管理员

  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('添加管理员失败:', err);
      return res.status(500).json({ success: false, message: '添加管理员失败' });
    }

    res.json({ success: true, message: '管理员添加成功' });
  });
});

// 删除管理员接口
app.get('/api/admins/delete', (req, res) => {
  const { username } = req.query; 
  if (!username) {
    return res.status(400).json({ success: false, message: '用户名不能为空' });
  }

  const sql = 'DELETE FROM users WHERE username = ?';
  connection.query(sql, [username], (err, result) => {
    if (err) {
      console.error('删除管理员失败:', err);
      return res.status(500).json({ success: false, message: '删除管理员失败' });
    }
    
    if (result.affectedRows === 0) {
      return res.json({ success: false, message: '未找到该管理员' });
    }
    
    res.json({ success: true, message: '管理员删除成功' });
  });
});


// 添加借书记录接口
app.post('/api/borrow/add', (req, res) => {
  console.log('接收到请求体:', req.body); // 添加日志
  
  const { borrower, book_name, grade } = req.body;
  
  // 更详细的参数验证
  if (!borrower || !book_name || !grade) {
    console.log('缺少参数:', { borrower, book_name, grade });
    return res.status(400).json({ 
      success: false, 
      message: '参数不完整',
      required: ['borrower', 'book_name', 'grade'],
      received: req.body
    });
  }

  const sql = `INSERT INTO borrow_records (borrower, book_name, grade) VALUES (?, ?, ?)`;
  
  connection.query(sql, [borrower, book_name, grade], (err, result) => {
    if (err) {
      console.error('数据库错误详情:', {
        message: err.message,
        sql: err.sql,
        stack: err.stack
      });
      return res.status(500).json({ 
        success: false, 
        message: '数据库操作失败',
        error: err.message
      });
    }
    console.log('插入成功:', result);
    res.json({ 
      success: true, 
      message: '添加成功',
      data: { id: result.insertId }
    });
  });
});

// 查询借书记录接口
app.get('/api/borrow/list', (req, res) => {
  const sql = `
    SELECT 
      id,
      borrower,
      book_name AS bookname,
      grade,
      DATE_FORMAT(borrow_time, '%Y-%m-%d %H:%i:%s') AS boorwerTime,
      CASE 
        WHEN is_returned = 1 THEN '已归还'
        ELSE '未归还'
      END AS \`return\`
    FROM borrow_records
    ORDER BY borrow_time DESC
  `;
  
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('查询借书记录失败:', {
        message: err.message,
        sql: err.sql,
        code: err.code
      });
      return res.status(500).json({ 
        success: false, 
        message: '查询失败',
        error: err.message
      });
    }
    
    res.json({ 
      success: true, 
      data: results
    });
  });
});

// 归还书籍接口
app.post('/api/borrow/return', (req, res) => {
  const { id } = req.body;
  
  if (!id) {
    return res.status(400).json({ 
      success: false, 
      message: '缺少借阅记录ID' 
    });
  }

  const sql = 'UPDATE borrow_records SET is_returned = 1 WHERE id = ?';
  
  connection.query(sql, [id], (err, result) => {
    if (err) {
      console.error('归还书籍失败:', err);
      return res.status(500).json({ 
        success: false, 
        message: '数据库操作失败',
        error: err.message
      });
    }
    
    if (result.affectedRows === 0) {
      return res.json({ 
        success: false, 
        message: '未找到该借阅记录' 
      });
    }
    
    res.json({ 
      success: true, 
      message: '归还成功' 
    });
  });
});

// 删除借阅记录接口
app.post('/api/borrow/delete', (req, res) => {
  const { id } = req.body;
  
  if (!id) {
    return res.status(400).json({ 
      success: false, 
      message: '缺少借阅记录ID' 
    });
  }

  const sql = 'DELETE FROM borrow_records WHERE id = ?';
  
  connection.query(sql, [id], (err, result) => {
    if (err) {
      console.error('删除借阅记录失败:', err);
      return res.status(500).json({ 
        success: false, 
        message: '数据库操作失败',
        error: err.message
      });
    }
    
    if (result.affectedRows === 0) {
      return res.json({ 
        success: false, 
        message: '未找到该借阅记录' 
      });
    }
    
    res.json({ 
      success: true, 
      message: '删除成功' 
    });
  });
});

// 查询各表数量接口
app.get('/api/count', (req, res) => {
  const queries = [
    'SELECT COUNT(*) AS bookCount FROM books',
    'SELECT COUNT(*) AS borrowCount FROM borrow_records',
    'SELECT COUNT(*) AS adminCount FROM users'
  ];

  let results = {};

  queries.forEach((query, index) => {
    connection.query(query, (err, rows) => {
      if (err) {
        console.error('数据库查询失败:', err.message);
        return res.status(500).json({ success: false, message: '数据库查询失败' });
      }

      if (index === 0) {
        results.bookCount = rows[0].bookCount;
      } else if (index === 1) {
        results.borrowCount = rows[0].borrowCount;
      } else if (index === 2) {
        results.adminCount = rows[0].adminCount;

        // 所有查询完成
        return res.json({
          success: true,
          data: {
            bookCount: results.bookCount,
            borrowCount: results.borrowCount,
            adminCount: results.adminCount
          }
        });
      }
    });
  });
});
// 定义服务监听的端口
const PORT = 3000;
app.listen(PORT, () => {
  console.log(`端口是: http://localhost:${PORT}`);
});